# 检测模块 - 包含所有数据检测功能
from config import load_env_structure_setl, load_env_structure_mdtrt, load_env_tbl_name, load_env_structure_fee
from sqlalchemy import text
import datetime

# 加载环境变量
(
    SETL_TBL_NAME, MDTRT_TBL_NAME, FEE_TBL_NAME, DX_TBL_NAME, TX_TBL_NAME
) = load_env_tbl_name()
(
    SETL_SETL_ID, SETL_MDTRT_ID, SETL_PSN_NO, SETL_HSP_ID, 
    SETL_SETL_TIME, SETL_GNR_C, SETL_GNR_B, SETL_IN_DATE_CHECK, SETL_OUT_DATE_CHECK, SETL_VALIDFLAG
) = load_env_structure_setl()
(
    MDTRT_MDTRT_ID, MDTRT_IN_OUT_ID, MDTRT_HSP_ID, MDTRT_HSP_NAME, MDTRT_HSP_LV, 
    MDTRT_PSN_NO, MDTRT_CERTNO, MDTRT_CERT_TYPE, MDTRT_PSN_NAME, MDTRT_GEND0, MDTRT_BRDY, 
    MDTRT_AGE, MDTRT_INSUTYPE0, MDTRT_IN_TIME, MDTRT_OUT_TIME, MDTRT_IN_DEPT, MDTRT_OUT_DEPT, 
    MDTRT_VALIDFLAG, MDTRT_OUT_DX_CODE_CHECK, MDTRT_OUT_DX_NAME_CHECK, MDTRT_OUTP_DX_CHECK
) = load_env_structure_mdtrt()
(
    FEE_SETL_ID, FEE_MDTRT_ID, FEE_HSP_ID, FEE_PSN_NO, FEE_MED_TYPE0,
    FEE_ITEM_TYPE, FEE_ITEM_TIME, FEE_ITEM_CODE, FEE_ITEM_NAME,
    FEE_ITEM_MED_CODE, FEE_ITEM_HSP_CODE, FEE_ITEM_HSP_NAME,
    FEE_ITEM_TYPE0, FEE_Q, FEE_P, FEE_C, FEE_B, FEE_VALIDFLAG
) = load_env_structure_fee()

def format_dt(dt):
    if isinstance(dt, str):
        return dt
    if dt is None:
        return ""
    if hasattr(dt, 'strftime'):
        return dt.strftime('%Y-%m-%d %H:%M:%S')
    return str(dt)

# 检测主单表中，是否有setl_id没有出现在费用表当中
def inspect_c01_setl_id_not_in_fee(engine, check_results, setl_count):
    """C01: 测试主单表的SETL_ID是否在费用表中存在"""
    print('============ C01：测试 SETL_ID 是否在费用表中存在 ============')

    sql = f"""
    SELECT {SETL_SETL_ID}
    FROM {SETL_TBL_NAME} S
    WHERE NOT EXISTS (
        SELECT 1 FROM {FEE_TBL_NAME} F WHERE F.{FEE_SETL_ID} = S.{SETL_SETL_ID}
    )
        AND S.{SETL_VALIDFLAG} = '1'
    """

    with engine.connect() as connection:
        result = connection.execute(text(sql))
        invalid_setl_ids = result.fetchmany(20)

    # if invalid_setl_ids:
    #     print("不存在于费用表的SETL_ID:")
    #     for row in invalid_setl_ids[:20]:
    #         print(row[0])
    
    # 将检测结果导入 dirty_data 表 (SETL_ID, INSPECT_ID, INSPECT_NAME, LV, INFO)
    sqlIns = f"""
    INSERT INTO DIRTY_DATA
    WITH T0 AS (
        SELECT {SETL_SETL_ID}
        FROM {SETL_TBL_NAME} S
        WHERE NOT EXISTS (
            SELECT 1 FROM {FEE_TBL_NAME} F WHERE F.{FEE_SETL_ID} = S.{SETL_SETL_ID} AND F.{FEE_VALIDFLAG} = '1'
        )
        AND S.{SETL_VALIDFLAG} = '1'
    )
    SELECT 
        {SETL_SETL_ID}, 
        'C01', 
        '结算表SETL_ID不存在于费用表中', 
        3, 
        {SETL_SETL_ID}
    FROM T0
    """
    with engine.connect() as connection:
        result_ins = connection.execute(text(sqlIns))
        inserted_count = result_ins.rowcount if hasattr(result_ins, 'rowcount') else None
        print(f"C01，结算表的SETL_ID不存在于费用表: {inserted_count}")
        print(f"占主单表总数量的比例：{inserted_count / setl_count * 100:.2f}%" if setl_count > 0 else "占主单表总数量的比例：0%")
        connection.commit()
    
    
    check_results.append({
        "inspect_id": "C01",
        "inspect_name": "测试主单表的SETL_ID是否在费用表中存在",
        "sql": sql,
        "msg": "不存在于费用表的SETL_ID",
        "info": [{"SETL_ID": row[0]} for row in invalid_setl_ids[:20]],
        "count": inserted_count if inserted_count is not None else 0,
        "ocp": inserted_count / setl_count * 100 if setl_count > 0 else 0
    })

    print('============ C01：测试 SETL_ID 是否在费用表中存在 FINISHED ============')

# 检测费用表中，费用发生时间早于入院时间当天的
def inspect_c02_fee_time_before_adm_time(engine, check_results, setl_count):
    """C02: 检测费用表中费用发生时间早于入院时间当天的记录"""
    print('============ C02：检测费用发生时间早于入院时间当天的记录 ============')
    
    # 如果FEE_ITEM_TIME或MDTRT_IN_TIME不存在，则跳过检测
    if not FEE_ITEM_TIME or not MDTRT_IN_TIME:
        print("FEE_ITEM_TIME或MDTRT_IN_TIME未定义，跳过C02检测")
        return

    sql = f"""
    SELECT f.{FEE_SETL_ID}, f.{FEE_ITEM_TIME}, m.{MDTRT_IN_TIME}
    FROM {FEE_TBL_NAME} f, {SETL_TBL_NAME} s, {MDTRT_TBL_NAME} m
    WHERE f.{FEE_SETL_ID} = s.{SETL_SETL_ID}
    AND s.{SETL_MDTRT_ID} = m.{MDTRT_MDTRT_ID}
    AND trunc(f.{FEE_ITEM_TIME}) < trunc(m.{MDTRT_IN_TIME})
    AND s.{SETL_VALIDFLAG} = '1'
    AND m.{MDTRT_VALIDFLAG} = '1'
    AND f.{FEE_VALIDFLAG} = '1'
    """

    with engine.connect() as connection:
        result = connection.execute(text(sql))
        invalid_rows = result.fetchmany(20)

    # if invalid_rows:
    #     print("费用发生时间早于入院时间当天的记录:")
    #     for row in invalid_rows[:20]:
    #         print(f"SETL_ID: {row[0]}, FEE_OCCUR_TIME: {row[1]}, ADM_TIME: {row[2]}")
    
    # 将检测结果导入 dirty_data 表 (SETL_ID, INSPECT_ID, INSPECT_NAME, LV, INFO)
    sqlIns = f"""
    INSERT INTO DIRTY_DATA
    WITH T0 AS (
        SELECT f.{FEE_SETL_ID}, f.{FEE_ITEM_TIME}, m.{MDTRT_IN_TIME}
        FROM {FEE_TBL_NAME} f, {SETL_TBL_NAME} s, {MDTRT_TBL_NAME} m
        WHERE f.{FEE_SETL_ID} = s.{SETL_SETL_ID}
        AND s.{SETL_MDTRT_ID} = m.{MDTRT_MDTRT_ID}
        AND trunc(f.{FEE_ITEM_TIME}) < trunc(m.{MDTRT_IN_TIME})
        AND s.{SETL_VALIDFLAG} = '1'
        AND m.{MDTRT_VALIDFLAG} = '1'
        AND f.{FEE_VALIDFLAG} = '1'
    )
    SELECT DISTINCT
        {FEE_SETL_ID},
        'C02',
        '费用表中ITEM_TIME早于就诊表的IN_TIME的记录',
        2,
        'ITEM_TIME: ' || {FEE_ITEM_TIME} || '; IN_TIME: ' || {MDTRT_IN_TIME}
    FROM T0
    """
    with engine.connect() as connection:
        result_ins = connection.execute(text(sqlIns))
        inserted_count = result_ins.rowcount if hasattr(result_ins, 'rowcount') else None
        print(f"C02，费用表中ITEM_TIME早于就诊表的IN_TIME的记录: {inserted_count}")
        print(f"占主单表总数量的比例：{inserted_count / setl_count * 100:.2f}%" if setl_count > 0 else "占主单表总数量的比例：0%")
        connection.commit()

    check_results.append({
        "inspect_id": "C02",
        "inspect_name": "检测费用表中费用发生时间早于入院时间当天的记录",
        "sql": sql,
        "msg": "费用发生时间早于入院时间当天",
        "info": [
            {"SETL_ID": row[0], "FEE_OCCUR_TIME": format_dt(row[1]), "ADM_TIME": format_dt(row[2])}
            for row in invalid_rows[:20]
        ],
        "count": inserted_count if inserted_count is not None else 0,
        "ocp": inserted_count / setl_count * 100 if setl_count > 0 else 0        
    })
    
    print('============ C02：检测费用发生时间早于入院时间当天的记录 FINISHED ============')

def inspect_c03_fee_time_after_dsc_time(engine, check_results, setl_count):
    """C03: 检测费用表中费用发生时间晚于出院时间当天的记录"""
    print('============ C03：检测费用发生时间晚于出院时间当天的记录 ============')
    
    # 如果FEE_ITEM_TIME或MDTRT_OUT_TIME不存在，则跳过检测
    if not FEE_ITEM_TIME or not MDTRT_OUT_TIME:
        print("FEE_ITEM_TIME或MDTRT_OUT_TIME未定义，跳过C03检测")
        return

    sql = f"""
    SELECT f.{FEE_SETL_ID}, f.{FEE_ITEM_TIME}, m.{MDTRT_OUT_TIME}
    FROM {FEE_TBL_NAME} f, {SETL_TBL_NAME} s, {MDTRT_TBL_NAME} m
    WHERE f.{FEE_SETL_ID} = s.{SETL_SETL_ID}
    AND s.{SETL_MDTRT_ID} = m.{MDTRT_MDTRT_ID}
    AND trunc(f.{FEE_ITEM_TIME}) > trunc(m.{MDTRT_OUT_TIME})
    AND s.{SETL_VALIDFLAG} = '1'
    AND m.{MDTRT_VALIDFLAG} = '1'
    AND f.{FEE_VALIDFLAG} = '1'
    """

    with engine.connect() as connection:
        result = connection.execute(text(sql))
        invalid_rows = result.fetchmany(20)

    # if invalid_rows:
    #     print("费用发生时间晚于出院时间当天的记录:")
    #     for row in invalid_rows[:20]:
    #         print(f"SETL_ID: {row[0]}, FEE_OCCUR_TIME: {row[1]}, DSC_TIME: {row[2]}")
    
    # 将检测结果导入 dirty_data 表 (SETL_ID, INSPECT_ID, INSPECT_NAME, LV, INFO)
    sqlIns = f"""
    INSERT INTO DIRTY_DATA
    WITH T0 AS (
        SELECT f.{FEE_SETL_ID}, f.{FEE_ITEM_TIME}, m.{MDTRT_OUT_TIME}
        FROM {FEE_TBL_NAME} f, {SETL_TBL_NAME} s, {MDTRT_TBL_NAME} m
        WHERE f.{FEE_SETL_ID} = s.{SETL_SETL_ID}
        AND s.{SETL_MDTRT_ID} = m.{MDTRT_MDTRT_ID}
        AND trunc(f.{FEE_ITEM_TIME}) > trunc(m.{MDTRT_OUT_TIME})
        AND s.{SETL_VALIDFLAG} = '1'
        AND m.{MDTRT_VALIDFLAG} = '1'
        AND f.{FEE_VALIDFLAG} = '1'
    )
    SELECT DISTINCT
        {FEE_SETL_ID},
        'C03',
        '费用表中ITEM_TIME晚于就诊表的OUT_TIME当天的记录',
        2,
        'ITEM_TIME: ' || {FEE_ITEM_TIME} || '; OUT_TIME: ' || {MDTRT_OUT_TIME}
    FROM T0
    """
    with engine.connect() as connection:
        result_ins = connection.execute(text(sqlIns))
        inserted_count = result_ins.rowcount if hasattr(result_ins, 'rowcount') else None
        print(f"C03，费用发生时间晚于出院时间当天的记录: {inserted_count}")
        print(f"占主单表总数量的比例：{inserted_count / setl_count * 100:.2f}%" if setl_count > 0 else "占主单表总数量的比例：0%")
        connection.commit()

    check_results.append({
        "inspect_id": "C03",
        "inspect_name": "检测费用表中费用发生时间晚于出院时间当天的记录",
        "sql": sql,
        "msg": "费用发生时间晚于出院时间当天",
        "info": [
            {"SETL_ID": row[0], "FEE_OCCUR_TIME": format_dt(row[1]), "DSC_TIME": format_dt(row[2])}
            for row in invalid_rows[:20]
        ],
        "count": inserted_count if inserted_count is not None else 0,
        "ocp": inserted_count / setl_count * 100 if setl_count > 0 else 0
    })

    print('============ C03：检测费用发生时间晚于出院时间当天的记录 FINISHED ============')

def inspect_c04_fee_sum_c_vs_setl_gnr_c(engine, check_results, setl_count):
    """C04: 检测费用表中setl_id的c的总和，与结算表中gnr_c的绝对值超过1的"""
    print('============ C04：检测费用表中setl_id的c的总和与结算表gnr_c的差值超过1的记录 ============')
    
    # 如果FEE_C或SETL_GNR_C不存在，则跳过检测
    if not FEE_C or not SETL_GNR_C:
        print("FEE_C或SETL_GNR_C未定义，跳过C04检测")
        return

    sql = f"""
    SELECT f.{FEE_SETL_ID}, SUM(f.{FEE_C}) AS fee_sum_c, s.{SETL_GNR_C}
    FROM {FEE_TBL_NAME} f, {SETL_TBL_NAME} s, {MDTRT_TBL_NAME} m
    WHERE f.{FEE_SETL_ID} = s.{SETL_SETL_ID}
    AND s.{SETL_MDTRT_ID} = m.{MDTRT_MDTRT_ID}
    AND s.{SETL_VALIDFLAG} = '1'
    AND m.{MDTRT_VALIDFLAG} = '1'
    AND f.{FEE_VALIDFLAG} = '1'
    GROUP BY f.{FEE_SETL_ID}, s.{SETL_GNR_C}
    HAVING ABS(SUM(f.{FEE_C}) - s.{SETL_GNR_C}) > 1
    """

    with engine.connect() as connection:
        result = connection.execute(text(sql))
        invalid_rows = result.fetchall()

    # if invalid_rows:
    #     print("费用表中setl_id的c的总和与结算表gnr_c的差值超过1的记录:")
    #     for row in invalid_rows[:20]:
    #         print(f"SETL_ID: {row[0]}, FEE_SUM_C: {row[1]}, SETL_GNR_C: {row[2]}, DIFF: {abs(row[1] - row[2])}")
    
    # 将检测结果导入 dirty_data 表 (SETL_ID, INSPECT_ID, INSPECT_NAME, LV, INFO)
    sqlIns = f"""
    INSERT INTO DIRTY_DATA
    WITH T0 AS (
        SELECT f.{FEE_SETL_ID}, SUM(f.{FEE_C}) AS fee_sum_c, s.{SETL_GNR_C}
        FROM {FEE_TBL_NAME} f, {SETL_TBL_NAME} s, {MDTRT_TBL_NAME} m
        WHERE f.{FEE_SETL_ID} = s.{SETL_SETL_ID}
        AND s.{SETL_MDTRT_ID} = m.{MDTRT_MDTRT_ID}
        AND s.{SETL_VALIDFLAG} = '1'
        AND m.{MDTRT_VALIDFLAG} = '1'
        AND f.{FEE_VALIDFLAG} = '1'
        GROUP BY f.{FEE_SETL_ID}, s.{SETL_GNR_C}
        HAVING ABS(SUM(f.{FEE_C}) - s.{SETL_GNR_C}) > 1
    )
    SELECT DISTINCT
        {FEE_SETL_ID},
        'C04',
        '费用表中C的总和与结算表的GNR_C的差值超过1',
        2,
        'FEE_SUM_C: ' || fee_sum_c || '; SETL_GNR_C: ' || {SETL_GNR_C} || '; DIFF: ' || ABS(fee_sum_c - {SETL_GNR_C})
    FROM T0
    """
    
    with engine.connect() as connection:
        result_ins = connection.execute(text(sqlIns))
        inserted_count = result_ins.rowcount if hasattr(result_ins, 'rowcount') else None
        print(f"C04，费用表中setl_id的c的总和与结算表gnr_c的差值超过1的记录: {inserted_count}")
        print(f"占主单表总数量的比例：{inserted_count / setl_count * 100:.2f}%" if setl_count > 0 else "占主单表总数量的比例：0%")
        connection.commit()

    check_results.append({
        "inspect_id": "C04",
        "inspect_name": "费用表中setl_id的c的总和与结算表gnr_c的差值超过1的记录",
        "sql": sql,
        "msg": "费用表中setl_id的c的总和与结算表gnr_c的差值超过1",
        "info": [
            {"SETL_ID": row[0], "FEE_SUM_C": float(row[1]), "SETL_GNR_C": float(row[2]), "DIFF": abs(float(row[1]) - float(row[2]))}
            for row in invalid_rows[:20]
        ],
        "count": inserted_count if inserted_count is not None else 0,
        "ocp": inserted_count / setl_count * 100 if setl_count > 0 else 0
    })

    print('============ C04：检测费用表中setl_id的c的总和与结算表gnr_c的差值超过1的记录 FINISHED ============')

# 检测费用表中setl_id的b的总和，与结算表中gnr_b的绝对值超过1的
def inspect_c05_fee_sum_b_vs_setl_gnr_b(engine, check_results, setl_count):
    """C05: 检测费用表中setl_id的b的总和，与结算表中gnr_b的绝对值超过1的"""
    print('============ C05：检测费用表中setl_id的b的总和与结算表gnr_b的差值超过1的记录 ============')
    
    # 如果FEE_B或SETL_GNR_B不存在，则跳过检测
    if not FEE_B or not SETL_GNR_B:
        print("FEE_B或SETL_GNR_B未定义，跳过C05检测")
        return

    sql = f"""
    SELECT f.{FEE_SETL_ID}, SUM(f.{FEE_B}) AS fee_sum_b, s.{SETL_GNR_B}
    FROM {FEE_TBL_NAME} f
    JOIN {SETL_TBL_NAME} s ON f.{FEE_SETL_ID} = s.{SETL_SETL_ID}
    WHERE f.{FEE_VALIDFLAG} = '1'
    AND s.{SETL_VALIDFLAG} = '1'
    GROUP BY f.{FEE_SETL_ID}, s.{SETL_GNR_B}
    HAVING ABS(SUM(f.{FEE_B}) - s.{SETL_GNR_B}) > 1
    """

    with engine.connect() as connection:
        result = connection.execute(text(sql))
        invalid_rows = result.fetchall()

    # if invalid_rows:
    #     print("费用表中setl_id的b的总和与结算表gnr_b的差值超过1的记录:")
    #     for row in invalid_rows[:20]:
    #         print(f"SETL_ID: {row[0]}, FEE_SUM_B: {row[1]}, SETL_GNR_B: {row[2]}, DIFF: {abs(row[1] - row[2])}")
    
    # 将检测结果导入 dirty_data 表 (SETL_ID, INSPECT_ID, INSPECT_NAME, LV, INFO)
    sqlIns = f"""
    INSERT INTO DIRTY_DATA
    WITH T0 AS (
        SELECT f.{FEE_SETL_ID}, SUM(f.{FEE_B}) AS fee_sum_b, s.{SETL_GNR_B}
        FROM {FEE_TBL_NAME} f
        JOIN {SETL_TBL_NAME} s ON f.{FEE_SETL_ID} = s.{SETL_SETL_ID}
        WHERE f.{FEE_VALIDFLAG} = '1'
        AND s.{SETL_VALIDFLAG} = '1'
        GROUP BY f.{FEE_SETL_ID}, s.{SETL_GNR_B}
        HAVING ABS(SUM(f.{FEE_B}) - s.{SETL_GNR_B}) > 1
    )
    SELECT DISTINCT
        {FEE_SETL_ID},
        'C05',
        '费用表中setl_id的b的总和与结算表gnr_b的差值超过1',
        2,
        'FEE_SUM_B: ' || fee_sum_b || '; SETL_GNR_B: ' || {SETL_GNR_B} || '; DIFF: ' || ABS(fee_sum_b - {SETL_GNR_B})
    FROM T0
    """
    with engine.connect() as connection:
        result_ins = connection.execute(text(sqlIns))
        inserted_count = result_ins.rowcount if hasattr(result_ins, 'rowcount') else None
        print(f"C05，费用表中setl_id的b的总和与结算表gnr_b的差值超过1的记录: {inserted_count}")
        print(f"占主单表总数量的比例：{inserted_count / setl_count * 100:.2f}%" if setl_count > 0 else "占主单表总数量的比例：0%")
        connection.commit()

    check_results.append({
        "inspect_id": "C05",
        "inspect_name": "费用表中setl_id的b的总和与结算表gnr_b的差值超过1的记录",
        "sql": sql,
        "msg": "费用表中setl_id的b的总和与结算表gnr_b的差值超过1",
        "info": [
            {"SETL_ID": row[0], "FEE_SUM_B": float(row[1]), "SETL_GNR_B": float(row[2]), "DIFF": abs(float(row[1]) - float(row[2]))}
            for row in invalid_rows[:20]
        ],
        "count": inserted_count if inserted_count is not None else 0,
        "ocp": inserted_count / setl_count * 100 if setl_count > 0 else 0
    })

    print('============ C05：检测费用表中setl_id的b的总和与结算表gnr_b的差值超过1的记录 FINISHED ============')

# 检测费用表中，Q 与 P 的乘积与 C 比较，差值超过1的
def inspect_c06_fee_qp_vs_c(engine, check_results, setl_count):
    """C06: 检测费用表中Q与P的乘积与C比较，差值超过1的"""
    print('============ C06：检测费用表中Q与P的乘积与C比较，差值超过1的记录 ============')
    
    # 如果FEE_Q、FEE_P或FEE_C不存在，则跳过检测
    if not FEE_Q or not FEE_P or not FEE_C:
        print("FEE_Q、FEE_P或FEE_C未定义，跳过C06检测")
        return

    sql = f"""
    SELECT f.{FEE_SETL_ID}, SUM(f.{FEE_Q} * f.{FEE_P}) AS qp_product, SUM(f.{FEE_C}) AS fee_sum_c
    FROM {FEE_TBL_NAME} f
    WHERE f.{FEE_VALIDFLAG} = '1'
    GROUP BY f.{FEE_SETL_ID}
    HAVING ABS(SUM(f.{FEE_Q} * f.{FEE_P}) - SUM(f.{FEE_C})) > 1
    """

    with engine.connect() as connection:
        result = connection.execute(text(sql))
        invalid_rows = result.fetchall()

    # if invalid_rows:
    #     print("费用表中Q与P的乘积与C比较，差值超过1的记录:")
    #     for row in invalid_rows[:20]:
    #         print(f"SETL_ID: {row[0]}, QP_PRODUCT: {row[1]}, FEE_SUM_C: {row[2]}, DIFF: {abs(row[1] - row[2])}")
    
    # 将检测结果导入 dirty_data 表 (SETL_ID, INSPECT_ID, INSPECT_NAME, LV, INFO)
    sqlIns = f"""
    INSERT INTO DIRTY_DATA
    WITH T0 AS (
        SELECT f.{FEE_SETL_ID}, SUM(f.{FEE_Q} * f.{FEE_P}) AS qp_product, SUM(f.{FEE_C}) AS fee_sum_c
        FROM {FEE_TBL_NAME} f
        WHERE f.{FEE_VALIDFLAG} = '1'
        GROUP BY f.{FEE_SETL_ID}
        HAVING ABS(SUM(f.{FEE_Q} * f.{FEE_P}) - SUM(f.{FEE_C})) > 1
    )
    SELECT DISTINCT
        {FEE_SETL_ID},
        'C06',
        '费用表中Q与P的乘积与C比较，差值超过1',
        2,
        'QP_PRODUCT: ' || qp_product || '; FEE_SUM_C: ' || fee_sum_c || '; DIFF: ' || ABS(qp_product - fee_sum_c)
    FROM T0
    """
    with engine.connect() as connection:
        result_ins = connection.execute(text(sqlIns))
        inserted_count = result_ins.rowcount if hasattr(result_ins, 'rowcount') else None
        print(f"C06，费用表中Q与P的乘积与C比较，差值超过1的记录: {inserted_count}")
        print(f"占主单表总数量的比例：{inserted_count / setl_count * 100:.2f}%" if setl_count > 0 else "占主单表总数量的比例：0%")
        connection.commit()
    check_results.append({
        "inspect_id": "C06",
        "inspect_name": "检测费用表中Q与P的乘积与C比较，差值超过1的记录",
        "sql": sql,
        "msg": "费用表中Q与P的乘积与C比较，差值超过1",
        "info": [
            {"SETL_ID": row[0], "QP_PRODUCT": float(row[1]), "FEE_SUM_C": float(row[2]), "DIFF": abs(float(row[1]) - float(row[2]))}
            for row in invalid_rows[:20]
        ],
        "count": inserted_count if inserted_count is not None else 0,
        "ocp": inserted_count / setl_count * 100 if setl_count > 0 else 0
    })
    print('============ C06：检测费用表中Q与P的乘积与C比较，差值超过1的记录 FINISHED ============')

# 检测费用表中同一个setl_id下，setl表中的mdtrt_id与fee表中的mdtrt_id不一致的
def inspect_c07_fee_mdtrt_id_vs_setl_mdtrt_id(engine, check_results, setl_count):
    """C07: 检测费用表中同一个setl_id下，setl表中的mdtrt_id与fee表中的mdtrt_id不一致的"""
    print('============ C07：检测费用表中同一个setl_id下，setl表中的mdtrt_id与fee表中的mdtrt_id不一致的记录 ============')
    
    # 如果SETL_MDTRT_ID或FEE_MDTRT_ID不存在，则跳过检测
    if not SETL_MDTRT_ID or not FEE_MDTRT_ID:
        print("SETL_MDTRT_ID或FEE_MDTRT_ID未定义，跳过C07检测")
        return

    sql = f"""
    SELECT DISTINCT f.{FEE_SETL_ID}, s.{SETL_MDTRT_ID}, f.{FEE_MDTRT_ID}
    FROM {FEE_TBL_NAME} f
    JOIN {SETL_TBL_NAME} s ON f.{FEE_SETL_ID} = s.{SETL_SETL_ID}
    WHERE f.{FEE_VALIDFLAG} = '1'
    AND s.{SETL_VALIDFLAG} = '1'
    AND s.{SETL_MDTRT_ID} <> f.{FEE_MDTRT_ID}
    """

    with engine.connect() as connection:
        result = connection.execute(text(sql))
        invalid_rows = result.fetchall()

    # if invalid_rows:
    #     print("费用表中同一个setl_id下，setl表中的mdtrt_id与fee表中的mdtrt_id不一致的记录:")
    #     for row in invalid_rows[:20]:
    #         print(f"SETL_ID: {row[0]}, SETL_MDTRT_ID: {row[1]}, FEE_MDTRT_ID: {row[2]}")
    
    # 将检测结果导入 dirty_data 表 (SETL_ID, INSPECT_ID, INSPECT_NAME, LV, INFO)
    sqlIns = f"""
    INSERT INTO DIRTY_DATA
    WITH T0 AS (
        SELECT DISTINCT f.{FEE_SETL_ID}, s.{SETL_MDTRT_ID}, f.{FEE_MDTRT_ID}
        FROM {FEE_TBL_NAME} f
        JOIN {SETL_TBL_NAME} s ON f.{FEE_SETL_ID} = s.{SETL_SETL_ID}
        WHERE f.{FEE_VALIDFLAG} = '1'
        AND s.{SETL_VALIDFLAG} = '1'
        AND s.{SETL_MDTRT_ID} <> f.{FEE_MDTRT_ID}
    )
    SELECT 
        {FEE_SETL_ID},
        'C07',
        '费用表中同一个setl_id下，setl表中的mdtrt_id与fee表中的mdtrt_id不一致',
        2,
        'SETL_MDTRT_ID: ' || {SETL_MDTRT_ID} || '; FEE_MDTRT_ID: ' || {FEE_MDTRT_ID}
    FROM T0
    """
    
    with engine.connect() as connection:
        result_ins = connection.execute(text(sqlIns))
        inserted_count = result_ins.rowcount if hasattr(result_ins, 'rowcount') else None
        print(f"C07，费用表中同一个setl_id下，setl表中的mdtrt_id与fee表中的mdtrt_id不一致的记录: {inserted_count}")
        print(f"占主单表总数量的比例：{inserted_count / setl_count * 100:.2f}%" if setl_count > 0 else "占主单表总数量的比例：0%")
        connection.commit()
        
    check_results.append({
        "inspect_id": "C07",
        "inspect_name": "检测费用表中同一个setl_id下，setl表中的mdtrt_id与fee表中的mdtrt_id不一致的记录",
        "sql": sql,
        "msg": "费用表中同一个setl_id下，setl表中的mdtrt_id与fee表中的mdtrt_id不一致",
        "info": [
            {"SETL_ID": row[0], "SETL_MDTRT_ID": row[1], "FEE_MDTRT_ID": row[2]}
            for row in invalid_rows[:20]
        ],
        "count": inserted_count if inserted_count is not None else 0,
        "ocp": inserted_count / setl_count * 100 if setl_count > 0 else 0
    })

# 检测费用表中同一个setl_id下，setl表中的psn_no与fee表中的psn_no不一致的
def inspect_c08_fee_psn_no_vs_setl_psn_no(engine, check_results, setl_count):
    """C08: 检测费用表中同一个setl_id下，setl表中的psn_no与fee表中的psn_no不一致的"""
    print('============ C08：检测费用表中同一个setl_id下，setl表中的psn_no与fee表中的psn_no不一致的记录 ============')

    # 如果SETL_PSN_NO或FEE_PSN_NO不存在，则跳过检测
    if not SETL_PSN_NO or not FEE_PSN_NO:
        print("SETL_PSN_NO或FEE_PSN_NO未定义，跳过C08检测")
        return

    sql = f"""
    SELECT DISTINCT f.{FEE_SETL_ID}, s.{SETL_PSN_NO}, f.{FEE_PSN_NO}
    FROM {FEE_TBL_NAME} f
    JOIN {SETL_TBL_NAME} s ON f.{FEE_SETL_ID} = s.{SETL_SETL_ID}
    WHERE f.{FEE_VALIDFLAG} = '1'
    AND s.{SETL_VALIDFLAG} = '1'
    AND s.{SETL_PSN_NO} <> f.{FEE_PSN_NO}
    """

    with engine.connect() as connection:
        result = connection.execute(text(sql))
        invalid_rows = result.fetchall()

    sqlIns = f"""
    INSERT INTO DIRTY_DATA
    WITH T0 AS (
        SELECT DISTINCT f.{FEE_SETL_ID}, s.{SETL_PSN_NO}, f.{FEE_PSN_NO}
        FROM {FEE_TBL_NAME} f
        JOIN {SETL_TBL_NAME} s ON f.{FEE_SETL_ID} = s.{SETL_SETL_ID}
        WHERE f.{FEE_VALIDFLAG} = '1'
        AND s.{SETL_VALIDFLAG} = '1'
        AND s.{SETL_PSN_NO} <> f.{FEE_PSN_NO}
    )
    SELECT 
        {FEE_SETL_ID},
        'C08',
        '费用表中同一个setl_id下，setl表中的psn_no与fee表中的psn_no不一致',
        2,
        'SETL_PSN_NO: ' || {SETL_PSN_NO} || '; FEE_PSN_NO: ' || {FEE_PSN_NO}
    FROM T0
    """

    with engine.connect() as connection:
        result_ins = connection.execute(text(sqlIns))
        inserted_count = result_ins.rowcount if hasattr(result_ins, 'rowcount') else None
        print(f"C08，费用表中同一个setl_id下，setl表中的psn_no与fee表中的psn_no不一致的记录: {inserted_count}")
        print(f"占主单表总数量的比例：{inserted_count / setl_count * 100:.2f}%" if setl_count > 0 else "占主单表总数量的比例：0%")
        connection.commit()

    check_results.append({
        "inspect_id": "C08",
        "inspect_name": "检测费用表中同一个setl_id下，setl表中的psn_no与fee表中的psn_no不一致的记录",
        "sql": sql,
        "msg": "费用表中同一个setl_id下，setl表中的psn_no与fee表中的psn_no不一致",
        "info": [
            {"SETL_ID": row[0], "SETL_PSN_NO": row[1], "FEE_PSN_NO": row[2]}
            for row in invalid_rows[:20]
        ],
        "count": inserted_count if inserted_count is not None else 0,
        "ocp": inserted_count / setl_count * 100 if setl_count > 0 else 0
    })

    print('============ C08：检测费用表中同一个setl_id下，setl表中的psn_no与fee表中的psn_no不一致的记录 FINISHED ============')

# 检测费用表中同一个setl_id下，setl表中的hsp_id与fee表中的hsp_id不一致的
def inspect_c09_fee_hsp_id_vs_setl_hsp_id(engine, check_results, setl_count):
    """C09: 检测费用表中同一个setl_id下，setl表中的hsp_id与fee表中的hsp_id不一致的"""
    print('============ C09：检测费用表中同一个setl_id下，setl表中的hsp_id与fee表中的hsp_id不一致的记录 ============')

    # 如果SETL_HSP_ID或FEE_HSP_ID不存在，则跳过检测
    if not SETL_HSP_ID or not FEE_HSP_ID:
        print("SETL_HSP_ID或FEE_HSP_ID未定义，跳过C09检测")
        return

    sql = f"""
    SELECT DISTINCT f.{FEE_SETL_ID}, s.{SETL_HSP_ID}, f.{FEE_HSP_ID}
    FROM {FEE_TBL_NAME} f
    JOIN {SETL_TBL_NAME} s ON f.{FEE_SETL_ID} = s.{SETL_SETL_ID}
    WHERE f.{FEE_VALIDFLAG} = '1'
    AND s.{SETL_VALIDFLAG} = '1'
    AND s.{SETL_HSP_ID} <> f.{FEE_HSP_ID}
    """

    with engine.connect() as connection:
        result = connection.execute(text(sql))
        invalid_rows = result.fetchall()

    sqlIns = f"""
    INSERT INTO DIRTY_DATA
    WITH T0 AS (
        SELECT DISTINCT f.{FEE_SETL_ID}, s.{SETL_HSP_ID}, f.{FEE_HSP_ID}
        FROM {FEE_TBL_NAME} f
        JOIN {SETL_TBL_NAME} s ON f.{FEE_SETL_ID} = s.{SETL_SETL_ID}
        WHERE f.{FEE_VALIDFLAG} = '1'
        AND s.{SETL_VALIDFLAG} = '1'
        AND s.{SETL_HSP_ID} <> f.{FEE_HSP_ID}
    )
    SELECT 
        {FEE_SETL_ID},
        'C09',
        '费用表中同一个setl_id下，setl表中的hsp_id与fee表中的hsp_id不一致',
        2,
        'SETL_HSP_ID: ' || {SETL_HSP_ID} || '; FEE_HSP_ID: ' || {FEE_HSP_ID}
    FROM T0
    """

    with engine.connect() as connection:
        result_ins = connection.execute(text(sqlIns))
        inserted_count = result_ins.rowcount if hasattr(result_ins, 'rowcount') else None
        print(f"C09，费用表中同一个setl_id下，setl表中的hsp_id与fee表中的hsp_id不一致的记录: {inserted_count}")
        print(f"占主单表总数量的比例：{inserted_count / setl_count * 100:.2f}%" if setl_count > 0 else "占主单表总数量的比例：0%")
        connection.commit()

    check_results.append({
        "inspect_id": "C09",
        "inspect_name": "检测费用表中同一个setl_id下，setl表中的hsp_id与fee表中的hsp_id不一致的记录",
        "sql": sql,
        "msg": "费用表中同一个setl_id下，setl表中的hsp_id与fee表中的hsp_id不一致",
        "info": [
            {"SETL_ID": row[0], "SETL_HSP_ID": row[1], "FEE_HSP_ID": row[2]}
            for row in invalid_rows[:20]
        ],
        "count": inserted_count if inserted_count is not None else 0,
        "ocp": inserted_count / setl_count * 100 if setl_count > 0 else 0
    })

    print('============ C09：检测费用表中同一个setl_id下，setl表中的hsp_id与fee表中的hsp_id不一致的记录 FINISHED ============')